﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BaiDuSitemap.DBO
{
    public class URL
    {
        /// <summary>
        /// 获取所有URL信息列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getAll4Sitemap()
        {
            string sql = "select a.[uid],a.[url],b.[gname],format$(a.[lastmod],\"yyyy-mm-dd\") as lastmod,c.[frequency],a.[priority] from ([QT_URL] a left join [QT_Group] b on a.[gid]=b.[gid]) left join [QT_Code_Frequency] c on a.[frequency]=c.[id] order by [gname] asc,[url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的URL信息列表
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <returns>DataTable</returns>
        public static DataTable get4Sitemap(int gid)
        {
            if (gid <= 0) return getAll4Sitemap();

            string sql = "select a.[uid],a.[url],b.[gname],format$(a.[lastmod],\"yyyy-mm-dd\") as lastmod,c.[frequency],a.[priority] from ([QT_URL] a left join [QT_Group] b on a.[gid]=b.[gid]) left join [QT_Code_Frequency] c on a.[frequency]=c.[id] where a.[gid]=" + gid + " order by [url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }
        /// <summary>
        /// 获取所有URL信息列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getAll4SnapShot()
        {
            string sql = "select a.[uid],a.[url],b.[gname],a.isshot,format$(a.[snapshot],\"yyyy-mm-dd\") as snapshot,format$(a.[checkdate],\"yyyy-mm-dd\") as checkdate from ([QT_URL] a left join [QT_Group] b on a.[gid]=b.[gid]) left join [QT_Code_Frequency] c on a.[frequency]=c.[id] order by [gname] asc,[url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的URL信息列表
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <returns>DataTable</returns>
        public static DataTable get4SnapShot(int gid)
        {
            if (gid <= 0) return getAll4SnapShot();

            string sql = "select a.[uid],a.[url],b.[gname],a.isshot,format$(a.[snapshot],\"yyyy-mm-dd\") as snapshot,format$(a.[checkdate],\"yyyy-mm-dd\") as checkdate from ([QT_URL] a left join [QT_Group] b on a.[gid]=b.[gid]) left join [QT_Code_Frequency] c on a.[frequency]=c.[id] where a.[gid]=" + gid + " order by [url] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的已收录网址数量
        /// </summary>
        /// <param name="gid">分组ID。&lt;=0表示搜索全部分组</param>
        /// <returns></returns>
        public static string get4SnapShotCount(int gid)
        {
            string sql = "select count(*) from [QT_URL] where [isshot]=1" + (gid <= 0 ? "" : (" and [gid]=" + gid));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt.Rows[0][0].ToString();
        }

        /// <summary>
        /// 添加网址到数据库。返回值-2为已存在，-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="url">网址</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Add(string url, int gid)
        {
            if (IsExsit(url, 0)) return -2;

            string sql = "insert into [QT_URL]([url],[gid]) values('" + url + "'," + gid + ")";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新单条网址信息(主信息)。返回值-2为不存在，-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="url">旧网址</param>
        /// <param name="newurl">新网址</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Update4Main(string url, string newurl, int gid)
        {
            if (!IsExsit(url, 0)) return -2;

            string sql = "update [QT_URL] set [url]=" + newurl + ",[gid]=" + gid + " where [url]='" + url + "'";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新一组网址信息(主信息)。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="urls">网址</param>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int Update4Main(List<string> urls, int gid)
        {
            string sql = "update [QT_URL] set [gid]=" + gid + " where [url] in('" + string.Join("','", urls.ToArray()) + "')";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新单条网址信息(快照信息)。返回值-2为不存在，-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="url"></param>
        /// <param name="hasData"></param>
        /// <param name="snapData"></param>
        /// <param name="checkData"></param>
        /// <returns></returns>
        public static int Update4SnapShot(string url, string hasDate, string snapDate, string checkDate)
        {
            if (!IsExsit(url, 0)) return -2;

            string sql = "update [QT_URL] set [isshot]=" + hasDate + ",[snapshot]=" + (snapDate == "" ? "null" : "#" + snapDate + "#") + ",[checkdate]=#" + checkDate + "# where [url]='" + url + "'";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新单条网址信息(地图信息)。返回值-2为不存在，-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="url">网址</param>
        /// <param name="lastmod">修改时间</param>
        /// <param name="frequency">更新频率</param>
        /// <param name="priority">优先级</param>
        /// <returns>int</returns>
        public static int Update4Sitemap(string url, string lastmod, string frequency, string priority)
        {
            if (!IsExsit(url, 0)) return -2;

            string sql = "update [QT_URL] set [lastmod]=" + (lastmod == "" ? "null" : ("#" + lastmod + "#")) + ",[frequency]=" + frequency + ",priority=" + priority + " where [url]='" + url + "'";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新多条网址信息(地图信息)。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="urls">网址</param>
        /// <param name="lastmod">修改时间</param>
        /// <param name="frequency">更新频率</param>
        /// <param name="priority">优先级</param>
        /// <returns>int</returns>
        public static int Update4Sitemap(List<string> urls, string lastmod, string frequency, string priority)
        {
            string sql = "update [QT_URL] set [lastmod]=" + (lastmod == "" ? "null" : ("#" + lastmod + "#")) + ",[frequency]=" + frequency + ",priority=" + priority + " where [url] in('" + string.Join("','", urls.ToArray()) + "')";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新一组网址信息(地图信息)。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <param name="lastmod">修改时间</param>
        /// <param name="frequency">更新频率</param>
        /// <param name="priority">优先级</param>
        /// <returns>int</returns>
        public static int Update4Sitemap(int gid, string lastmod, string frequency, string priority)
        {
            string sql = "update [QT_URL] set [lastmod]=" + (lastmod == "" ? "null" : ("#" + lastmod + "#")) + ",[frequency]=" + frequency + ",priority=" + priority + " where [gid] =" + gid;
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除单条网址。返回值-2为不存在，-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="url">网址</param>
        /// <returns>int</returns>
        public static int Delete(string url)
        {
            if (!IsExsit(url, 0)) return -2;

            string sql = "delete from [QT_URL] where [url]='" + url + "'";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除一组网址。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="urls">网址</param>
        /// <returns>int</returns>
        public static int Delete(List<string> urls)
        {
            string sql = "delete from [QT_URL] where [url] in('" + string.Join("','", urls.ToArray()) + "')";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除指定（单个）分组的全部网址。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <returns>int</returns>
        public static int DeleteByGroup(int gid)
        {
            string sql = "delete from [QT_URL]" + (gid <= 0 ? "" : (" where [gid]=" + gid));
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除指定（多个）分组的全部网址。返回值-1为失败，>=0为操作记录条数
        /// </summary>
        /// <param name="gids">分组ID</param>
        /// <returns>int</returns>
        public static int DeleteByGroup(List<string> gids)
        {
            string sql = "delete from [QT_URL] where [gid] in(" + string.Join(",", gids.ToArray()) + ")";
            return new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 判断网址是否存在
        /// </summary>
        /// <param name="url">URL地址</param>
        /// <param name="uid">网址ID。0代表不指定ID</param>
        /// <returns>bool</returns>
        private static bool IsExsit(string url,int uid)
        {
            string sql = "select [uid] from [QT_URL] where [url]='" + url + "'" + (uid == 0 ? "" : (" and [uid]=" + uid));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }
         

    }
}
